package com.project.dao.impl;

import com.project.bean.CutPageBean;
import com.project.bean.ProductBean;
import com.project.dao.IProductDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品持久接口实现类
 */
@Repository
public class ProductDaoImpl extends BaseDao implements IProductDao {
    @Override
    public CutPageBean<ProductBean> findByItem(int pageNO, String name,
                    LocalDate startDate, LocalDate endDate) {
        CutPageBean<ProductBean> cutBean = new CutPageBean<>();
//        this.setConnection();
//        String sql = "select  * from t_product where 1=1 ";
//        String dynaSql = "";
//
//        if(name != null && name.length() != 0){
//            dynaSql += "and p_name like '%"+name+"%' ";
//        }
//        if(startDate != null){
//            dynaSql += "and p_createDate >= '"+startDate+"' ";
//        }
//        if(endDate != null){
//            dynaSql += "and p_createDate <= '" + endDate+"' ";
//        }
//
//        try {
//            ps = con.prepareStatement(sql+dynaSql+" limit ?,?");
//            ps.setObject(1,(pageNO - 1)*CutPageBean.PAGESIZE);
//            ps.setObject(2,CutPageBean.PAGESIZE);
//
//            rs = ps.executeQuery();
//            while(rs.next()){
//                cutBean.getList().add(this.getProduct());
//            }
//
//            ps = con.prepareStatement("select count(*) c from t_product where 1=1 "+dynaSql);
//            rs = ps.executeQuery();
//            if(rs.next()) {
//                cutBean.setCount(rs.getInt("c"));
//            }
//
//        } catch (SQLException throwables) {
//            throwables.printStackTrace();
//        }finally{
//            this.closeConnection();
//        }

        this.setConnection();
        String dynaSql = " where 1=1 ";
        List valueList = new ArrayList<>();
        if(name != null && name.length() != 0){
            dynaSql += "and p_name like ? ";
            valueList.add("%"+name+"%");
        }
        if(startDate != null){
            dynaSql += "and p_createDate >= ? ";
            valueList.add(startDate);
        }
        if(endDate != null){
            dynaSql += "and p_createDate <= ? ";
            valueList.add(endDate);
        }
        try {
            ps = con.prepareStatement("select * from t_product "+dynaSql+" limit ?,?");
            for(int i=0;i<valueList.size();i++){
                ps.setObject(i+1,valueList.get(i));
            }
            ps.setObject(valueList.size() + 1,(pageNO - 1)*CutPageBean.PAGESIZE);
            ps.setObject(valueList.size() + 2,CutPageBean.PAGESIZE);

            rs = ps.executeQuery();
            while(rs.next()){
                cutBean.getList().add(this.getProduct());
            }

            ps = con.prepareStatement("select count(*) c from t_product "+dynaSql);
            for(int i=0;i<valueList.size();i++){
                ps.setObject(i+1,valueList.get(i));
            }

            rs = ps.executeQuery();
            if(rs.next()){
                cutBean.setCount(rs.getInt("c"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            this.closeConnection();
        }

        return cutBean;
    }

    @Override
    public ProductBean findById(Integer id) {
        ProductBean product = null;
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_product where pk_productId=?");
            ps.setObject(1,id);

            rs = ps.executeQuery();
            if(rs.next()){
               product = this.getProduct();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            this.closeConnection();
        }

        return product;
    }

    private ProductBean getProduct() throws SQLException {
        ProductBean product = new ProductBean();
        product.setId(rs.getInt("pk_productId"));
        product.setName(rs.getString("p_name"));
        product.setCreateDate(LocalDate.parse(rs.getString("p_createDate")));
        product.setPrice(rs.getInt("p_price"));
        return product;
    }

    @Override
    public void update(Integer id, Integer price) {
        this.setConnection();
        try {
            ps = con.prepareStatement("update t_product set p_price=? where pk_productId=?");
            ps.setObject(1,price);
            ps.setObject(2,id);

            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            this.closeConnection();
        }
    }
}
